from datetime import datetime
from typing import List, Optional
from sqlalchemy.orm import Session

from src.database.dao.base_dao import BaseDAO
from src.database.manager import DBManager
from src.database.models import AudioUpload

#####Abandoned code for migration, kept for reference
class AudioUploadDAO(BaseDAO):
    """Data Access Object for FileUpload operations."""
    def __init__(self, db: Session = None):
        self.db = db if db else DBManager().get_session()
        

    def create(self, audio_path: str, filename: str,
            db: Session=None, 
            order: int = 0,
            group_id: Optional[str] = None,
            meta_data: Optional[dict] = None, 
            subtitle_path: Optional[str] = None,
            status: Optional[str] = None) -> AudioUpload:
        """Create a new file upload record."""
        
        # 确保有活跃的会话
        close_session = False
        if db is None:
            db = DBManager().get_session()
            close_session = True
        
        try:
            # 创建对象时不指定ID，让SQLAlchemy自动生成
            file_upload = AudioUpload(
                audio_path=audio_path,
                filename=filename,
                group_id=group_id,
                order=order,
                meta_data=meta_data,
                subtitle_path=subtitle_path,
                status=status
            )
            
            db.add(file_upload)
            db.flush()  # 刷新以获取生成的ID，但不提交
            
            # 只有在我们创建了会话时才提交和关闭
            if close_session:
                db.commit()
                
            return file_upload
            
        except Exception as e:
            # 回滚事务
            db.rollback()
            from src.utils.logger import log_error
            log_error(f"创建文件上传记录失败: {e}")
            raise
            
        finally:
            # 如果我们创建了会话，则负责关闭它
            if close_session and db:
                db.close()

    def get_by_id(self, id: int) -> Optional[AudioUpload]:
        """Retrieve a file upload record by ID."""
        return self.db.query(AudioUpload).filter(AudioUpload.id == id).first()

    def get_all(self, db: Session, skip: int = 0, limit: int = 100) -> List[AudioUpload]:
        """Retrieve all file upload records with pagination."""
        return db.query(AudioUpload).offset(skip).limit(limit).all()

    def update_status(self, db: Session, file_upload_id: int, status: str) -> Optional[AudioUpload]:
        """Update the status of a file upload."""
        file_upload = self.get_by_id(db, file_upload_id)
        if file_upload:
            file_upload.status = status
            db.commit()
            db.refresh(file_upload)
        return file_upload

    def get_by_group_id(self, group_id: str) -> List[AudioUpload]:
        """Retrieve all file uploads for a specific group."""
        res = self.db.query(AudioUpload).filter(AudioUpload.group_id == group_id).all()
        return self.to_dict_list(res)

    def delete(self, db: Session, file_upload_id: int) -> bool:
        """Delete a file upload record."""
        file_upload = self.get_by_id(db, file_upload_id)
        if file_upload:
            db.delete(file_upload)
            db.commit()
            return True
        return False
    
    def get_audio_history(self, page=1, page_size=10):
        """
        获取音频上传历史记录，按时间排序分页返回
        
        :param page: 页码，从1开始
        :param page_size: 每页记录数
        :return: 包含记录和分页信息的字典
        """
        try:
            # 计算分页偏移量
            offset = (page - 1) * page_size
            
            # 查询总记录数
            total_count = self.db.query(AudioUpload).count()
            
            # 查询分页数据，按created_at降序排列（最新的在前）
            query = self.db.query(AudioUpload)
            query = query.order_by(AudioUpload.created_at.desc())
            query = query.offset(offset).limit(page_size)
            records = query.all()
            
            # 计算总页数
            total_pages = (total_count + page_size - 1) // page_size
            
            # 将ORM对象转换为字典
            records_dict = self.to_dict_list(records)
            
            return {
                "success": True,
                "data": {
                    "records": records_dict,
                    "pagination": {
                        "page": page,
                        "page_size": page_size,
                        "total_count": total_count,
                        "total_pages": total_pages
                    }
                }
            }
        except Exception as e:
            from src.utils.logger import log_error
            log_error(f"查询音频历史记录失败: {e}")
            return {
                "success": False,
                "message": f"查询音频历史记录失败: {e}"
            }